package dbutils;

import java.util.List;
import java.util.Map;
import java.util.StringJoiner;

/**
 * @author Xiongx
 * @version 1.0
 * @date 2021/6/29 16:00
 * @since JDK 1.8
 */
public class SqlScript {

    public static void main(String[] args) {
        DjwContentClean();
       // OracleDemo();
//        DBSql dbSql=getDM_DEV_DJ();
//        List<Map<String, Object>> mapList=dbSql.selectMapObjList(" select * from jc_sys_user limit 1");
//        mapList.forEach(e->{
//            System.out.println(e);
//        });
    //deleteTxtMap();

//        StringJoiner stringJoiner=new StringJoiner(";");
//        System.out.println("===================");
//        for (int i = 0; i < mapList.size(); i++) {
//            Map<String, Object> stringObjectMap=mapList.get(i);
//            Integer id=(Integer)stringObjectMap.get("id");
//            Integer sort_num=(Integer)stringObjectMap.get("sort_num");
//            sort_num=sort_num+i;
//            String update=String.format("update jc_content set sort_num='%s' where id='%s' ",sort_num,id);
//           stringJoiner.add(update);
//        }
//        dbSql.sqlExecute(stringJoiner.toString());
//        System.out.println("zhiiiii");
    }


    /**
     * 内容清除
     */
    public static void DjwContentClean(){
        Map<String, String> dmdata=DataScore.QA_DJ();
        DBSql dbSql=new DBSql(dmdata);
        List<Map<String, Object>> mapList=dbSql.selectMapObjList("select id from jc_content where deleted_flag='1'");
        mapList.forEach(e->{
            System.out.println(e);
        });

    }

    public static void SqlServer(){
        Map<String, String> sqlServer=DataScore.sqlServer();
        DBSql dbSql=new DBSql(sqlServer);
        List<Map<String, Object>> maps=dbSql.selectMapObjList("SELECT TOP 1000 * FROM [dbo].[Whir_Dev_Column]");
        maps.forEach(e->{
            System.out.println(e);
        });
    }

    public static void updateJcContent(){

        DBSql dbSql=getDM_DEV_DJ();
        List<Map<String, Object>> mapList=dbSql.selectMapObjList("select * from jc_content");
        StringJoiner stringJoiner=new StringJoiner(";");
        System.out.println("===================");
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> stringObjectMap=mapList.get(i);
            Integer id=(Integer)stringObjectMap.get("id");
            Integer sort_num=(Integer)stringObjectMap.get("sort_num");
            sort_num=sort_num+i;
            String update=String.format("update jc_content set sort_num='%s' where id='%s' ",sort_num,id);
           stringJoiner.add(update);
        }
        dbSql.sqlExecute(stringJoiner.toString());
    }

    public static void OracleDemo(){
       DBSql dbSql=getORACLE();
       List<Map<String, Object>>mapList=dbSql.selectMapObjList("select * from JC_CONTENT");
       mapList.forEach(e->{
           System.out.println(e);
       });
    }

    public static void deleteTxtMap(){
        DBSql dbSql=getDM_DEV_DJ();
        List<Map<String, Object>> maps=dbSql.selectMapObjList("select * from jc_content_txt where attr_key='txtmap'");

        String s=deleteListSql("jc_content_txt",maps,"id");
        System.out.println(s);
//        maps.forEach(e->{
//            String str=deleteSqlCreate("jc_content_txt","id",e.get("id"));
//            System.out.println(str);
//        });
    }

    public static String deleteListSql(String tableName,List<Map<String, Object>> mapList,String key){
        StringJoiner stringJoiner=new StringJoiner(";");
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map=mapList.get(i);
            String str=deleteSqlCreate(tableName,key,map.get(key));
            stringJoiner.add(str);
        }
        return stringJoiner.toString();
    }

    private static String deleteSqlCreate(String tableName,String key,Object value){
        StringBuffer stringBuffer=new StringBuffer();
        stringBuffer.append("delete ").append(tableName)
                .append(" where ").append(key).append(" ='")
                .append(value).append("'");
        return stringBuffer.toString();
    }

    public static void deleteContent(){
        DBSql dbSql=getDM_DEV_DJ();
        List<Map<String, Object>> maps=dbSql.selectMapObjList("select * from jc_content where deleted_flag='1' ");
        StringJoiner stringJoiner=new StringJoiner(";");
        maps.forEach(e->{
            String deleteContentattr=String.format("delete jc_content_attr where content_id='%s'",e.get("id"));
            String deleteCotent=String.format("delete jc_content where id='%s'",e.get("id"));
            String deleteCOntenttxt=String.format("delete jc_content_txt where content_id='%s'",e.get("id"));
            stringJoiner.add(deleteContentattr+"\n");
            stringJoiner.add(deleteCotent+"\n");
            stringJoiner.add(deleteCOntenttxt+"\n");
        });
        dbSql.sqlExecute(stringJoiner.toString());

        System.out.println(stringJoiner.toString());
    }

    public static String updateSql(String tableName,String title,Integer id){
        StringBuilder stringBuilder=new StringBuilder();
        stringBuilder.append("update ").append(tableName).append(" set title='")
                .append(title).append("' where id=").append(id);
        return stringBuilder.toString();
    }

    public static DBSql getDM_DEV_DJ(){
        Map<String, String> stringMap=DataScore.DEV_DJ();
        return new DBSql(stringMap);
    }

    public static DBSql getORACLE(){
        Map<String, String> stringMap=DataScore.ORACLE();
        return new DBSql(stringMap);
    }

    /**
     * 修改标题
     */
    public static void updateTitle(){
        List<Map<String, Object>> maps=getDM_DEV_DJ().selectMapObjList("select * from jc_content where deleted_flag='0' and ( title like '%<br/>%' or title like '%<br>%')");
        maps.forEach(e->{
            String title=(String)e.get("title");
            title=title.replaceAll("<br/>","").replaceAll("<br>","");
            Integer id=(Integer) e.get("id");
            //   String usql=updateSql("jc_content",title,id);
            // System.out.println(usql);
            //getDM_DEV_DJ().sqlExecute(usql);
        });
    }
}
